#Include "Fileio.ch"
#Include "Topconn.ch"
#Include "tbiconn.ch"

/*
+----------------------------------------------------------------------------+
!                             FICHA TECNICA DO PROGRAMA                      !
+----------------------------------------------------------------------------+
!Programa          ! CPL06DMS                                                !
+------------------+---------------------------------------------------------+
!Descricao         ! DMS                                                     !
!                  ! ImportStock.                                            !
!                  ! importacao estoque diario.                              !
!                  !                                                         !
+------------------+---------------------------------------------------------+
!Autor             ! TSC505 - Felipe Jose Limas                              !
+------------------+---------------------------------------------------------+
!Data de Criacao   ! 31/05/12                                                !
+------------------+---------------------------------------------------------+
!   ATUALIZACOES                                                             !
+-------------------------------------------+-----------+-----------+--------+
!   Descricao detalhada da atualizacao      !Nome do    ! Analista  !Data da !
!                                           !Solicitante! Respons.  !Atualiz.!
+-------------------------------------------+-----------+-----------+--------+
!                                           !           !           !        !
!                                           !           !           !        !
+-------------------------------------------+-----------+-----------+--------+
!                                           !           !           !        !
!                                           !           !           !        !
+-------------------------------------------+-----------+-----------+--------+
*/

User Function CPL06DMS()
Local nHandle :=-1
Local cLinha  :=""
Local cQuery  :=""
Local cAli    :=GetNextAlias()
Local cArq    :=StrTran("\dms\gerados\ImportStock_"+DtoS(dDataBase)+Time()+".txt", ":", "")

//campos do arquivo
Local   cdProduct            :=""
Local   vlMonthAmount        :=""
Local   vlMonthQuantity      :=""
Local   vlReceivedQuantity   :=""
Local   vlDispatchedQuantity :=""
Local   dtStok               :=""
Local   cdStatus             :=""
Local nQtdR:= 0
Private	cxData               :=DTOC(getmv("MV_ULMES"))

cxData                       :=SubStr((cxData), 7, 4)+SubStr((cxData), 4, 2)+SubStr((cxData), 1, 2)


cQuery:= " SELECT * "
cQuery+= " FROM "+RetSqlName("SB1")+" SB1, "
cQuery+= "      "+RetSqlName("SB9")+" SB9 "
cQuery+= " WHERE B1_COD = B9_COD AND "
cQuery+= "       B1_LOCPAD = B9_LOCAL AND "
cQuery+= "       B1_ENVDMIS = 'S' AND "
cQuery+= "       B1_LOCPAD = '01' AND "
cQuery+= "       B1_MSBLQL = '2' AND "
cQuery+= "       B9_DATA = '"+cxData+"' AND "
cQuery+= "      "+RetSqlCond("SB1")+" AND "
cQuery+= "      "+RetSqlCond("SB9")

TCQuery ChangeQuery(cQuery) New Alias &(cAli)

Count to nQtdR

If nQtdR > 0
	nHandle:= FCREATE(cArq)
	If nHandle == -1
		conout("Erro ao criar arquivo - ferror " + Str(Ferror()))
	Else
		&(cAli)->(dbGoTop())
		
		Do While !&(cAli)->(EOF())
			cdProduct           :=Alltrim(&(cAli)->(B1_ZSKU))+Chr(9)
			vlMonthAmount       :=Alltrim(Str(&(cAli)->(B9_VINI1)))+Chr(9)
			vlMonthQuantity     :=Alltrim(Str(&(cAli)->(B9_QINI)))+Chr(9)
			vlReceivedQuantity  :=Alltrim(StrTran(StrTran(cValToChar(GetEnt(&(cAli)->(B1_COD))), '.', ''), ',', '.'))+Chr(9)
			vlDispatchedQuantity:=Alltrim(StrTran(StrTran(cValToChar(GetSai(&(cAli)->(B1_COD))), '.', ''), ',', '.'))+Chr(9)
			dtStok              :=Left(DtoS(dDataBase), 4)+"-"+SubStr(DtoS(dDataBase), 5, 2)+"-"+Right(DtoS(dDataBase), 2)+Chr(9)
			cdStatus            :=IIF(Alltrim(&(cAli)->(B1_MSBLQL)) == "1", "DEACT", "ACT")+Chr(9)
			
			cLinha:= cdProduct+vlMonthAmount+vlMonthQuantity+vlReceivedQuantity+vlDispatchedQuantity+dtStok+cdStatus
			FWrite(nHandle, cLinha+Chr(13)+Chr(10))
			
			&(cAli)->(dbSkip())
		EndDo
		
		&(cAli)->(dbCloseArea())
		FClose(nHandle)
		
		/*Tenta se conectar ao servidor ftp na porta 21
		//conexao sera feita com programa externo ws_ftp - gabriel 17/10/2012
		If !FTPCONNECT("189.59.223.136" , 21 ,"FTP_UDB_CPL", "16Access97")
			conout("Nao foi possivel conectar")
			Return .F.
		EndIf
		
		If !FTPUPLOAD(cArq, "/FTP_UDB_CPL/Inbox/"+SubStr(cArq, At("IMPORT", UPPER(cArq))))
			conout("Nao foi possivel realizar o upload!!")
			Return .F.
		EndIf
		
		FTPDISCONNECT()
		*/
	EndIf
EndIf

Return cArq

***********************************
//Busca as saidas de um produto
Static Function GetSai(cProd)
***********************************
Local nRet:= 0
Local aArea:= GetArea()
Local cQuery:= ""
Local cAli:= GetNextAlias()

cQuery:= " SELECT SUM(FT_QUANT) QTD "
cQuery+= " FROM "+RetSqlName("SFT")+" SFT "
cQuery+= " WHERE FT_PRODUTO = '"+cProd+"' AND "
cQuery+= "       FT_ENTRADA BETWEEN '"+cxData+"' AND '"+DtoS(dDataBase)+"' AND "
cQuery+= "       FT_TIPOMOV = 'S' AND "
cQuery+= "       FT_ESTOQUE = 'S' AND "
cQuery+= "       FT_CFOP BETWEEN '5000' AND '9999' AND "
cQuery+= "     "+RetSqlCond("SFT")
TCQuery ChangeQuery(cQuery) New Alias &(cAli)
nRet:= &(cAli)->(QTD)
&(cAli)->(dbCloseArea())

RestArea(aArea)
Return nRet

************************************
//Busca as entradas de um produto
Static Function GetEnt(cProd)
************************************
Local nRet:= 0
Local aArea:= GetArea()
Local cQuery:= ""
Local cAli:= GetNextAlias()

cQuery:= " SELECT SUM(FT_QUANT) QTD "
cQuery+= " FROM "+RetSqlName("SFT")+" SFT "
cQuery+= " WHERE FT_PRODUTO = '"+cProd+"' AND "
cQuery+= "       FT_ENTRADA BETWEEN '"+cxData+"' AND '"+DtoS(dDataBase)+"' AND "
cQuery+= "       FT_TIPOMOV = 'E' AND "
cQuery+= "       FT_ESTOQUE = 'S' AND "
cQuery+= "       FT_CFOP BETWEEN '0000' AND '4999' AND "
cQuery+= "     "+RetSqlCond("SFT")
TCQuery ChangeQuery(cQuery) New Alias &(cAli)
nRet:= &(cAli)->(QTD)
&(cAli)->(dbCloseArea())

RestArea(aArea)
Return nRet